1. Field of the Invention
This invention relates to backup, restoration and migration systems of a program and data in a database system.
2. Description of the Related Art
A relational database is rapidly prevailing. In the relational database, an international standard language which is called SQL (JIS X3005 ISO/IEC 9075:Database Language SQL) is commonly used as a database language. Therefore, the relational database and the international standard database language SQL are assumed to be used in the following explanations.
FIG. 15 shows a basic configuration of an application program and a database.
Generally, a database system is composed of a database definition where contents of the data are described and the application program which operates in reference with the definition in addition to the processing data. The definition is called a catalog, repository, data directory/dictionary, etc. Furthermore, the data are always updated and the update history is stored in a special file. The special file which stores the update history is called a journal, log, etc.
Meanwhile, the database language such as SQL is a language to process the database (to retrieve required data from the database, to update, delete and insert the data in the database, to define the data to manipulate the data in the database, etc.). The database language is unable to program the process of communication, display control, etc. which is necessary for operations. Therefore, it is common in the application program that the program related to the database process is written in SQL and the rest is written in a conventional language such as COBOL, etc.
When a program which contains a SQL statement is compiled, a part which is written in SQL is compiled to a SQL procedure, while the rest is compiled as a normal load module. The SQL statement is replaced by a procedure call (sql.sub.-- exec in FIG. 15) which is set to retrieve the SQL procedure which is complied via a database system kernel. Furthermore, an ID of the SQL procedure which is registered in the database system is given as an argument of the procedure call.
It differs according to the realization methods of the system, but it is common that the load module and the SQL procedure which are generated by using SQL are registered as a part of the definition in the SQL system.
FIG. 15 shows the basic configuration of the application program and the database. In FIG. 15, the SQL program is inserted in a COBOL application program.
The SQL program is compiled by a compiler. When the compiler compiles the SQL program, an ID is given to each SQL program. In FIG. 15, ID No. 23 and ID No. 24 are given to respective SQL procedures corresponding to the SQL program.
Meanwhile, the COBOL application program is compiled to an executable form of the load module. The load module contains a call statement to execute the SQL procedure. The call statement calls the SQL procedure registered in the catalog by using the ID of the SQL procedure NO. 23 and No. 24 as arguments. Accordingly, the database process which is written in the SQL procedure is executed.
As FIG. 16 shows, the application program is executed like a normal program until the procedure call (sql.sub.-- exec) for the database system process. Then, the SQL procedure corresponding to the procedure call is retrieved via the database system kernel and executed. As described, the application program accesses the database appropriately and executes the required processes.
The definition of the database is explained in the following.
The database system creates and controls the definition related to the database in various ways. For example, in SQL, the database information is controlled concentrically to avoid double defining of the data and to realize effective control of stored information in the database. A group of the definitions is called a catalog. In SQL, the followings are objects to be controlled in the system:
(1) A Real Table
The real table is the data corresponding to a conventional file. A field which constitutes the real table is called a column. Each column has attributes such as a data type, length, etc.
(2) A View
The view corresponds to a virtual file, etc. The view shows a result to be obtained by applying conditions to the real table, and is treated as if a real table. The data are presented in modified forms in addition to the original form. Consequently, illegal access to the data is prevented and common patterns of accesses are merged into view definitions. Even a complex case, where multiple real tables (files) are joined and presented in one table (file), is realized by views. Furthermore, the view is also created by referring to other (multiple) views. Both of the real table and the view are generally called a table.
(3) A Schema
The schema corresponds to a conventional directory, etc. The schema is a container to arrange the tables, etc. separately. For example, when a schema name is S1 and a table name is T1, the table (the real table and the view) is named S1.T1 by placing the schema name on the top.
(4) A Database User
(5) Privilege of the Database Users
Concerning the operation of the real table and the view, the user who created the table owns all the privileges (to select, update, delete and insert). When the privileges are granted to other users appropriately, the accesses to the database are able to be controlled in a certain group of users.
In SQL, the privileges to access the data are controlled strictly and the privileges to select, update, delete and insert the data are controlled accordingly for each real table and view and for each user. Basically, the privileges are owned by the creators of the real tables, views, etc, but the privileges may be granted to others. Furthermore, the granting action itself may also be allowed to others. To allow the granting action is called as granting the privileges "WITH GRANT OPTION."
(6) The Application Program of the Database and the SQL Procedure Used in the Program
In SQL, the above-described (1)-(6) are controlled in the catalog as the following information, for example:
a) Information of user catalog (user name, number of schemas owned) PA1 b) Information of schema catalog (schema name, owner name, number of tables stored) PA1 c) Information of table catalog (table name, column number, belonging schema name, view or real table) PA1 d) Information of column catalog (column name, belonging table name, belonging schema name, data type, length) PA1 e) Information of view definition catalog (view name, belonging schema name, view definition) PA1 f) Information of reference relation of view and table (view name, belonging schema name, referred table name, referred table's belonging schema name) PA1 g) Information of authority catalog (grantor, receiver, type, object, schema name of the object, with grant option or not) PA1 h) Information of program catalog (application program ID, location of program in OS) PA1 i) Information of procedure catalog (application program ID, procedure ID, procedure) A catalog of the SQL procedures compiled and generated corresponding to the SQL command used in the above application program PA1 j) Information of reference relation of procedure and table PA1 a) necessary parts are acknowledged and extracted in a necessary order by a human, PA1 b) furthermore, restoration is made carefully by a human in a migrated location. PA1 (a) database access means for accessing the database system PA1 (b) storage medium access means for saving the backup objects as backup data to the storage medium PA1 (c) command interpreter means for inputting and interpreting a backup command of the database system PA1 (d) backup procedure generator means for accessing the definition in the database system through the database access means and generating a backup procedure for saving the backup objects, and PA1 (e) control means for identifying the backup objects in the database system based on the backup command interpreted by the command interpreter means, giving the backup objects to the backup procedure generator means so as to get the backup procedure, extracting the backup objects from the database system with the database access means according to the backup procedure, and storing the backup objects to the storage medium with the storage medium access means. PA1 (a) inputting a backup request of the database system; PA1 (b) identifying data to be saved according to the backup request; PA1 (c) saving the identified data in an executable form to a storage medium; PA1 (d) inputting one of a restore request and a migrate request; and PA1 (e) executing the saved data in the storage medium in the executable form based on one of the restore request and the migrate request so as to restore data or migrate data.
User names and numbers of schemas owned by the users
Schema names, names of schema owners, a number of tables in the schema
This information controls both real tables and views together as tables. This information includes table names, a number of columns, schema names of the tables, and real tables or views.
Information for each column. Column names, table names which the columns belong to, schema names which the tables belong to, data type, length, etc.
This information controls definition for each view. This information includes view names, schema names which the views belong to and view definition.
The views are created by referring to other views and real tables. For each referred table, there is a view name, schema name which the view belongs to, table name which the view refers to and schema name which the table belongs to.
This information is related to the privileges granted to database users to access the tables. The privileges correspond to conventional privileges like readable, writable, executable, etc. For example, this information includes grantors, receivers, privilege types (SELECT, UPDATE, INSERT AND DELETE privileges for the concerning table), tables concerning the privileges, with grant option or not.
Especially, when a new table is created, the creator is supposed to receive the privileges from the system and a special value (.sub.-- system) is set for the grantors of the privileges.
In order to create a view, a SELECT privilege is at least necessary. When the created views don't satisfy certain conditions (such as referring to multiple tables, etc.), update becomes impossible and the UPDATE, DELETE AND INSERT privileges for the views are not generated.
A catalog of application programs
This information includes the ID and the locations of the program in OS (directory, etc).
This information includes program ID, SQL procedure ID and procedure.
(application program ID, procedure ID, schema name, table name)
This information controls tables referred to by each procedure of application program. This information is provided for each procedure and for each referred table.
Samples of a database and a catalog are explained in the following. Contents of the database in FIG. 17 are controlled as catalog information shown in FIGS. 18-27.
In the database shown in FIG. 17, there are two users, Suzuki and Tanaka (see FIG. 18). The user Suzuki owns schema S1 and the user Tanaka owns schema S2 and S3 (see FIG. 19). Furthermore, the schema S1 contains real table T1 and view V1. The schema S2 contains real table T2 and view V2 and the schema S3 contains view V3 (see FIG. 20).
The view V1 refers to T1 (see FIG. 23). The definition is as follows:
SELECT * FROM T1 WHERE t11&gt;100 (see FIG. 22) The view V1 is updatable (see FIG. 24).
The view V2 refers to T1 and T2 (see FIG. 23). The definition is as follows:
SELECT t11, t12, t21 FROM T1, T2 WHERE t1=t23 (see FIG. 22)
The view V2 is not updatable as multiple tables are referred to (see FIG. 24). Since the view V2 refers to the table T1 owned by the other person (Suzuki), a SELECT privilege must be granted to Tanaka by Suzuki in advance (see FIG. 24).
The view V3 refers to V2 (see FIG. 23). The definition is as follows:
SELECT v21 FROM V2 (see FIG. 22)
The view V3 is not updatable as multiple tables are referred to (see FIG. 24).
Program PROG1 contains two SQL statements. They are SQL procedure P1 and P2 (see FIG. 25 and FIG. 26). Meanwhile, program PROG2 contains a SQL statement, SQL procedure P3 (see FIG. 25 and FIG. 26).
The SQL procedure P1 refers to V2 (see FIG. 27). The SQL procedure P2 refers to T2 (see FIG. 27). The SQL procedure P3 refers to V1 and V3 (see FIG. 27).
Furthermore, Suzuki grants Tanaka a privilege to select (including refer) concerning T1 with grant option (see FIG. 24).
As described, the catalog information in FIGS. 18-27 are samples of the definition in the database.
Furthermore, the database system which uses SQL, etc. accompanies the following characteristics:
(1) A history of all the updates, deletion, etc. executed to the database is stored in the journal. Therefore, even if the database is cleared or damaged, it is able to be recovered always.
(2) When the database is changed by adding index, changing access privilege. etc., the SQL procedure registered in the catalog is no more an optimal procedure. In that case, when the SQL procedure is executed via the COBOL application program, the concerning SQL procedure is dynamically recompiled and an optimal procedure is re-generated in accordance with the change. The procedure is registered in the catalog and the execution resumes.
Problems to be Solved by the Invention
The above-described characteristics are realized. However, the following problems exist;
(1) All the definitions of the database including other developing applications are controlled together in a catalog in a system. Therefore, when a developed application is intended to be packaged, it is difficult to extract only necessary parts to be packaged. For example, a real table is referred to by multiple views and the view is referred to by the multiple views. Additionally, a privilege is necessary to refer to a view and a condition where the privileges are defined must be reproduced every time, when a view is created.
For example, in the above-mentioned case, the tables T1 and T2 must be created in order to create the view V2. In addition, the schema S1 and S2 and owners of each schema, user Suzuki and user Tanaka must be generated in order to create the table T1 and T2. Furthermore, after the tables T1 and T2 are created, the SELECT privilege must be granted to Tanaka by Suzuki. Otherwise, Tanaka is unable to refer to T1 and the view V2 is not created.
As described, the definitions are complicatedly related to each other. Additionally, it is necessary to restore the definition historically in a chronological order. At present,
As described, conventional creation and diffusion of a packaged software is difficult. The complicated installation and development by a human has been necessary for each concerning system.
(2) Recently, many machines are connected via LAN (Local Area Network). It is getting a popular style that when a software is developed in a machine, the result is distributed to other multiple machines via LAN. However, even if the system provides such environment, it has been difficult for the application program which accesses the database to utilize the environment effectively. It has been also difficult to distribute the software due to the above-described problems.
(3) Additionally, CASE (Computer Aided Software Engineering) which controls the whole software engineering is prevailing. In CASE, repository which controls the definition in the engineering work is similar to the described catalog. Therefore, the problem mentioned in (1) occurs, when engineering work environment is migrated or the engineering work is distributed to multiple groups.
(4) Possibly, the database itself needs to accompany special functions in addition to the conventional functions in order to restore the definition historically in the migrated location.
For example, when the information on a number of tables in each schema is migrated, the whole content of the catalog including the information are saved. When the package is installed, the saved information may be retrieved and the concerning part of the catalog in the system may be updated directly in the migrated location. However, in that case, a special interface for the catalog processing (direct update of the part of the catalog) is necessary besides the normal database function. Even if the package is developed by using a standard language like COBOL and SQL, since the special interface depends on the specifications of venders, the migration of the system among different venders is impossible.
(5) The conventional journal stores the update history for the whole database system. Therefore, the whole database must be stored (backuped) in order to restore the database. Even if it is known that only a small part of the whole database has been updated, the whole database is always backuped. Therefore, maintenance operation time tends to be long.
(6) In the migration of the system, it is difficult to create an ID in order to keep the corresponding relationships of the application program and the SQL procedure registered in the catalog.
Especially, when the application program is migrated to another machine or another SQL system, the migrated ID might conflict with the ID which is already assigned in the operating application program and the SQL procedure in the machine. Therefore, the migration is difficult. Additionally, in the migrated location, the migrated SQL procedure might not be compiled to an optimal SQL procedure always due to the data volume, etc.